package com.bingxue.edu.management.report.service;

import com.bingxue.edu.common.enums.StudentClassEnum;
import com.bingxue.edu.framework.mybatis.query.QueryBuilder;
import com.bingxue.edu.management.education.service.LessonStudentService;
import com.bingxue.edu.management.report.model.query.LessonStatQuery;
import com.bingxue.edu.management.report.model.resp.LessonStatListResp;
import com.bingxue.edu.management.report.model.resp.LessonStatReportResp;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.util.List;

import static com.bingxue.edu.management.education.model.entity.table.ClassInfoTableDef.CLASS_INFO;
import static com.bingxue.edu.management.education.model.entity.table.LessonStudentTableDef.LESSON_STUDENT;
import static com.bingxue.edu.management.education.model.entity.table.LessonTableDef.LESSON;
import static com.bingxue.edu.management.education.model.entity.table.StudentTableDef.STUDENT;
import static com.bingxue.edu.management.system.model.entity.table.EmployeeTableDef.EMPLOYEE;
import static com.mybatisflex.core.query.QueryMethods.case_;
import static com.mybatisflex.core.query.QueryMethods.column;
import static com.mybatisflex.core.query.QueryMethods.sum;

/**
 * @author panbo
 * @since 2024/12/30
 */
@Service
@RequiredArgsConstructor
public class LessonStatService {

    private final LessonStudentService lessonStudentService;

    public List<LessonStatReportResp> getLessonStatReport(LessonStatQuery query) {
        QueryWrapper subQuery = QueryBuilder.build(query, true)
                .select(
                        LESSON_STUDENT.LESSON_CLASS_ID.as("classId"),
                        CLASS_INFO.NAME.as("className"),
                        EMPLOYEE.NAME.as("leadTeacherName"),
                        LESSON_STUDENT.SIGN_IN_STATUS.as("signInStatus"),
                        sum(LESSON_STUDENT.LESSON_PRICE).as("lessonPrice")
                )
                .from(LESSON_STUDENT)
                .leftJoin(LESSON).on(LESSON_STUDENT.LESSON_ID.eq(LESSON.ID))
                .leftJoin(CLASS_INFO).on(LESSON_STUDENT.LESSON_CLASS_ID.eq(CLASS_INFO.ID))
                .leftJoin(EMPLOYEE).on(CLASS_INFO.LEAD_TEACHER_ID.eq(EMPLOYEE.ID))
                .where(LESSON.LESSON_DATE.ge(query.getDateFrom()))
                .and(LESSON.LESSON_DATE.lt(query.getDateTo().plusDays(1)))
                .and(LESSON_STUDENT.STUDENT_CLASS_STATUS.eq(StudentClassEnum.NORMAL))
                .and(LESSON_STUDENT.DELETED.eq(false))
                .groupBy(LESSON_STUDENT.LESSON_CLASS_ID, LESSON_STUDENT.SIGN_IN_STATUS);

        QueryWrapper queryWrapper = new QueryWrapper()
                .with("CTE").asSelect(subQuery)
                .select(column("classId"),
                        column("className"),
                        column("leadTeacherName"),
                        sum(case_(column("signInStatus")).when(1).then(column("lessonPrice")).else_(0).end()).as("consumedLessonPrice"),
                        sum(case_(column("signInStatus")).when(0).then(column("lessonPrice")).else_(0).end()).as("remainedLessonPrice")
                )
                .from("CTE")
                .groupBy(column("classId"));

        return lessonStudentService.getMapper().selectListByQueryAs(queryWrapper, LessonStatReportResp.class);
    }

    public List<LessonStatListResp> getLessonStatList(LessonStatQuery query) {
        QueryWrapper subQuery = QueryBuilder.build(query, true)
                .select(
                        LESSON_STUDENT.STUDENT_ID.as("studentId"),
                        STUDENT.NAME.as("studentName"),
                        LESSON_STUDENT.LESSON_CLASS_ID.as("classId"),
                        CLASS_INFO.NAME.as("className"),
                        LESSON_STUDENT.SIGN_IN_STATUS.as("signInStatus"),
                        sum(LESSON_STUDENT.LESSON_PRICE).as("lessonPrice")
                )
                .from(LESSON_STUDENT)
                .leftJoin(STUDENT).on(LESSON_STUDENT.STUDENT_ID.eq(STUDENT.ID))
                .leftJoin(LESSON).on(LESSON_STUDENT.LESSON_ID.eq(LESSON.ID))
                .leftJoin(CLASS_INFO).on(LESSON_STUDENT.LESSON_CLASS_ID.eq(CLASS_INFO.ID))
                .where(LESSON.LESSON_DATE.ge(query.getDateFrom()))
                .and(LESSON.LESSON_DATE.lt(query.getDateTo().plusDays(1)))
                .and(LESSON_STUDENT.STUDENT_CLASS_STATUS.eq(StudentClassEnum.NORMAL))
                .groupBy(LESSON_STUDENT.STUDENT_ID, LESSON_STUDENT.LESSON_CLASS_ID, LESSON_STUDENT.SIGN_IN_STATUS);

        QueryWrapper queryWrapper = new QueryWrapper()
                .with("CTE").asSelect(subQuery)
                .select(column("studentId"),
                        column("studentName"),
                        column("classId"),
                        column("className"),
                        sum(case_(column("signInStatus")).when(1).then(column("lessonPrice")).else_(0).end()).as("consumedLessonPrice"),
                        sum(case_(column("signInStatus")).when(0).then(column("lessonPrice")).else_(0).end()).as("remainedLessonPrice")
                )
                .from("CTE")
                .groupBy(column("studentId"), column("classId"));

        return lessonStudentService.getMapper().selectListByQueryAs(queryWrapper, LessonStatListResp.class);
    }
}
